﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using RN.ERP.DAL.Context;
using System.Data.OleDb;

namespace RN.ERP.DAL.Common
{
    public class CommonHelper
    {
        private ZBDL_Context zbdlcontext = new ZBDL_Context();

        /// <summary>
        /// 执行sql返回DataTable（带分页）
        /// </summary>
        /// <param name="Sql"></param>
        /// <param name="startRecord"></param>
        /// <param name="maxRecord"></param>
        /// <param name="Tb"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataTable GetTable(string Sql, SqlParameter[] parameters, int startRecord, int maxRecord, string Tb, out int count)
        {
            DataTable tb = new DataTable();
            using (SqlConnection connection = new SqlConnection(zbdlcontext.Database.Connection.ConnectionString))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand(Sql, connection);
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet, startRecord, maxRecord, Tb);
                DataTable table = dataSet.Tables[Tb];
                dataAdapter.Fill(tb);
                count = tb.Rows.Count;
                connection.Close();
                connection.Dispose();
                return table;
            }
        }

        /// <summary>
        /// 从excel中获取DataTable
        /// </summary>
        /// <param name="uploadPath"></param>
        /// <returns></returns>
        public DataTable GetExcelTable(string uploadPath)
        {
            DataSet ds = new DataSet();
            ////HDR为yes 则第一数据行为列名,为no则自动为列加列名F1  F2  F3
            string Xls_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
            OleDbConnection Conn = new OleDbConnection(Xls_ConnStr);
            try
            {
                Conn.Open();
                string sql_str = "select * from [Sheet1$]";
                OleDbDataAdapter da = new OleDbDataAdapter(sql_str, Conn);
                da.Fill(ds, "excel_data");
                Conn.Close();
            }
            catch
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }
                return null;
            }
            finally
            {
                Conn.Dispose();
            }

            if (ds == null)
            {
                return null;
            }

            if (ds.Tables.Count < 1)
            {
                return null;
            }

            return ds.Tables[0];
        }
    
    }
}
